﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;

using HsTec.Mobile.Green.Util;

namespace HsTec.Mobile.Green.DAL
{
    /// <summary>
    /// 数据访问类IndexFormula。
    /// </summary>
    public class IndexFormulaDAL
    {
        public IndexFormulaDAL()
        { }
        #region  成员方法
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string sExistsWhere)
        {
            return SQLHelper.Exists("tb_IndexFormula", sExistsWhere);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(HsTec.Mobile.Green.Model.IndexFormula model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into tb_IndexFormula(");
            strSql.Append("IndexName,FormulaNo,Formula,FormulaParent,OutOf,FormulaSQL,YearState,FormulaChildCount,IsProcedure,IndexSQL)");
            strSql.Append(" values (");
            strSql.Append("@IndexName,@FormulaNo,@Formula,@FormulaParent,@OutOf,@FormulaSQL,@YearState,@FormulaChildCount,@IsProcedure,@IndexSQL)");
            strSql.Append(";select @@IDENTITY");
            SqlParameter[] parameters = {
					new SqlParameter("@IndexName", SqlDbType.NVarChar,25),
					new SqlParameter("@FormulaNo", SqlDbType.VarChar,200),
					new SqlParameter("@Formula", SqlDbType.NVarChar,200),
					new SqlParameter("@FormulaParent", SqlDbType.Int,4),
					new SqlParameter("@OutOf", SqlDbType.Int,4),
					new SqlParameter("@FormulaSQL", SqlDbType.NVarChar,800),
					new SqlParameter("@YearState", SqlDbType.Char,1),
					new SqlParameter("@FormulaChildCount", SqlDbType.Int,4),
					new SqlParameter("@IsProcedure", SqlDbType.Int,4),
					new SqlParameter("@IndexSQL", SqlDbType.Text)};
            parameters[0].Value = model.IndexName;
            parameters[1].Value = model.FormulaNo;
            parameters[2].Value = model.Formula;
            parameters[3].Value = model.FormulaParent;
            parameters[4].Value = model.OutOf;
            parameters[5].Value = model.FormulaSQL;
            parameters[6].Value = model.YearState;
            parameters[7].Value = model.FormulaChildCount;
            parameters[8].Value = model.IsProcedure;
            parameters[9].Value = model.IndexSQL;

            return SQLHelper.ExecuteNonQuery(strSql.ToString(), parameters);
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public int Update(HsTec.Mobile.Green.Model.IndexFormula model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tb_IndexFormula set ");
            strSql.Append("IndexName=@IndexName,");
            strSql.Append("FormulaNo=@FormulaNo,");
            strSql.Append("Formula=@Formula,");
            strSql.Append("FormulaParent=@FormulaParent,");
            strSql.Append("OutOf=@OutOf,");
            strSql.Append("FormulaSQL=@FormulaSQL,");
            strSql.Append("YearState=@YearState,");
            strSql.Append("FormulaChildCount=@FormulaChildCount,");
            strSql.Append("IsProcedure=@IsProcedure,");
            strSql.Append("IndexSQL=@IndexSQL");
            strSql.Append(" where IF_ID=@IF_ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@IF_ID", SqlDbType.Int,4),
					new SqlParameter("@IndexName", SqlDbType.NVarChar,25),
					new SqlParameter("@FormulaNo", SqlDbType.VarChar,200),
					new SqlParameter("@Formula", SqlDbType.NVarChar,200),
					new SqlParameter("@FormulaParent", SqlDbType.Int,4),
					new SqlParameter("@OutOf", SqlDbType.Int,4),
					new SqlParameter("@FormulaSQL", SqlDbType.NVarChar,800),
					new SqlParameter("@YearState", SqlDbType.Char,1),
					new SqlParameter("@FormulaChildCount", SqlDbType.Int,4),
					new SqlParameter("@IsProcedure", SqlDbType.Int,4),
					new SqlParameter("@IndexSQL", SqlDbType.Text)};
            parameters[0].Value = model.IF_ID;
            parameters[1].Value = model.IndexName;
            parameters[2].Value = model.FormulaNo;
            parameters[3].Value = model.Formula;
            parameters[4].Value = model.FormulaParent;
            parameters[5].Value = model.OutOf;
            parameters[6].Value = model.FormulaSQL;
            parameters[7].Value = model.YearState;
            parameters[8].Value = model.FormulaChildCount;
            parameters[9].Value = model.IsProcedure;
            parameters[10].Value = model.IndexSQL;

            return SQLHelper.ExecuteNonQuery(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public int Delete(string sIDs)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tb_IndexFormula ");
            strSql.Append(" where IF_ID in ( " + sIDs + " )");
            return SQLHelper.ExecuteNonQuery(strSql.ToString());
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public HsTec.Mobile.Green.Model.IndexFormula GetModel(int IF_ID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 IF_ID,IndexName,FormulaNo,Formula,FormulaParent,OutOf,FormulaSQL,YearState,FormulaChildCount,IsProcedure,IndexSQL from tb_IndexFormula ");
            strSql.Append(" where IF_ID=@IF_ID ");
            SqlParameter[] parameters = {
					new SqlParameter("@IF_ID", SqlDbType.Int,4)};
            parameters[0].Value = IF_ID;

            HsTec.Mobile.Green.Model.IndexFormula model = new HsTec.Mobile.Green.Model.IndexFormula();
            DataSet ds = SQLHelper.ExecuteDataset(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["IF_ID"].ToString() != "")
                {
                    model.IF_ID = int.Parse(ds.Tables[0].Rows[0]["IF_ID"].ToString());
                }
                model.IndexName = ds.Tables[0].Rows[0]["IndexName"].ToString();
                model.FormulaNo = ds.Tables[0].Rows[0]["FormulaNo"].ToString();
                model.Formula = ds.Tables[0].Rows[0]["Formula"].ToString();
                if (ds.Tables[0].Rows[0]["FormulaParent"].ToString() != "")
                {
                    model.FormulaParent = int.Parse(ds.Tables[0].Rows[0]["FormulaParent"].ToString());
                }
                if (ds.Tables[0].Rows[0]["OutOf"].ToString() != "")
                {
                    model.OutOf = int.Parse(ds.Tables[0].Rows[0]["OutOf"].ToString());
                }
                model.FormulaSQL = ds.Tables[0].Rows[0]["FormulaSQL"].ToString();
                model.YearState = ds.Tables[0].Rows[0]["YearState"].ToString();
                if (ds.Tables[0].Rows[0]["FormulaChildCount"].ToString() != "")
                {
                    model.FormulaChildCount = int.Parse(ds.Tables[0].Rows[0]["FormulaChildCount"].ToString());
                }
                model.IsProcedure = ds.Tables[0].Rows[0]["IsProcedure"].ToString();
                model.IndexSQL = ds.Tables[0].Rows[0]["IndexSQL"].ToString();
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataView GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select IF_ID,IndexName,FormulaNo,Formula,FormulaParent,OutOf,FormulaSQL,YearState,FormulaChildCount,IsProcedure,IndexSQL ");
            strSql.Append(" FROM tb_IndexFormula ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return SQLHelper.ExecuteDataView(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataView GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" IF_ID,IndexName,FormulaNo,Formula,FormulaParent,OutOf,FormulaSQL,YearState,FormulaChildCount,IsProcedure,IndexSQL ");
            strSql.Append(" FROM tb_IndexFormula ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return SQLHelper.ExecuteDataView(strSql.ToString());
        }

        #endregion  成员方法
    }
}

